USE OILManagement;
GO
--sell statatistic for each product
IF OBJECT_ID('SellStatistics', 'P') IS NOT NULL
	DROP PROCEDURE SellStatistics;
GO

CREATE PROCEDURE SellStatistics
	@from DATETIME,
	@to DATETIME
AS

	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
		
	SELECT StoreID,
	       Product,
		   --DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0) AS Date,
	       SUM(Price * Amount) AS Total
	       --SUM(Paid) AS Paid
	FROM   Sells
	WHERE  SellTime >= @from
	       AND SellTime < @to
	GROUP BY
	       StoreID,
	       Product
		   --DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0)
GO
--sell statatistic for all
IF OBJECT_ID('SellStatistics_All', 'P') IS NOT NULL
	DROP PROCEDURE SellStatistics_All;
GO

CREATE PROCEDURE SellStatistics_All
	@from DATETIME,
	@to DATETIME
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT StoreID,
		   --DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0) AS Date,	       
	       SUM(Price * Amount) AS Total
	       --SUM(Paid) AS Paid
	FROM   Sells
	WHERE  SellTime >= @from
	       AND SellTime < @to
	GROUP BY
	       StoreID
		   --DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0)
GO
--payment statistic
IF OBJECT_ID('PaymentStatistics', 'P') IS NOT NULL
	DROP PROCEDURE PaymentStatistics;
GO

CREATE PROCEDURE PaymentStatistics
	@from DATETIME,
	@to DATETIME
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT p.StoreID,
	       --DATEADD(dd, (DATEDIFF(dd, 0, p.PayTime)), 0) AS Date,
	       SUM(p.Amount) AS TotalAmount
	FROM   Payments p
	WHERE  p.PayTime >= @from
	       AND p.PayTime < @to
	GROUP BY
	       p.StoreID
	       --DATEADD(dd, (DATEDIFF(dd, 0, p.PayTime)), 0)
GO

--expense statistic
IF OBJECT_ID('ExpenseStatistics', 'P') IS NOT NULL
	DROP PROCEDURE ExpenseStatistics;
GO
CREATE PROCEDURE ExpenseStatistics
	@from DATETIME,
	@to DATETIME
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT e.StoreID,
	       --DATEADD(dd, (DATEDIFF(dd, 0, e.ExpenseTime)), 0) AS Date,
	       SUM(e.Amount) AS TotalExpensed
	FROM   Expenses e
	WHERE  e.ExpenseTime >= @from
	       AND e.ExpenseTime < @to
	GROUP BY
	       e.StoreID
	       --DATEADD(dd, (DATEDIFF(dd, 0, e.ExpenseTime)), 0)
GO

--debt statistics in range of time
IF OBJECT_ID('DebtStatistics', 'P') IS NOT NULL
	DROP PROCEDURE DebtStatistics;
GO

CREATE PROCEDURE DebtStatistics
	@from DATETIME,
	@to DATETIME
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT s.StoreID,
	       --DATEADD(dd, (DATEDIFF(dd, 0, s.SellTime)), 0) AS Date,
	       SUM(s.Amount) AS TotalDebt
	FROM   Debts s
	WHERE  s.DebtTime >= @from
	       AND s.DebtTime < @to
	GROUP BY
	       s.StoreID
	       --DATEADD(dd, (DATEDIFF(dd, 0, s.SellTime)), 0)
GO

--debt statistics for all time
IF OBJECT_ID('DebtStatistics_All', 'P') IS NOT NULL
	DROP PROCEDURE DebtStatistics_All;
GO

CREATE PROCEDURE DebtStatistics_All
AS
	SELECT t.Customer,
	       c.Company,
	       t.Debt
	FROM   (
	           SELECT b.Customer,
	                  b.TotalDebt - CASE 
	                                      WHEN p.TotalPaid IS NULL THEN 0
	                                      ELSE p.TotalPaid
	                                 END AS Debt
	           FROM   (
	                      SELECT Customer,
	                             SUM(Amount) AS TotalDebt
	                      FROM   Debts
	                      GROUP BY
	                             Customer
	                  ) b
	                  LEFT OUTER JOIN (
	                           SELECT p.Customer,
	                                  SUM(p.Amount) AS TotalPaid
	                           FROM   Payments p
	                           GROUP BY
	                                  p.Customer
	                       ) p
	                       ON  b.Customer = p.Customer
	       ) t
	       JOIN Customers c
	            ON  t.Customer = c.ID
	WHERE (t.Debt > 0)
GO

IF OBJECT_ID('StoreStatistics', 'P') IS NOT NULL
	DROP PROCEDURE StoreStatistics;
GO

CREATE PROCEDURE StoreStatistics
	@from DATETIME,
	@to DATETIME
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT CASE 
	            WHEN t.StoreID IS NULL THEN y.StoreID
	            ELSE t.StoreID
	       END AS StoreID,
	       /* CASE 
	            WHEN y.Date IS NULL THEN t.Date
	            ELSE y.Date
	       END AS Date, */
	       CASE 
	            WHEN y.TotalIncome IS NULL THEN 0
	            ELSE y.TotalIncome
	       END AS TotalIncome,
	       CASE 
	            WHEN t.TotalExpense IS NULL THEN 0
	            ELSE t.TotalExpense
	       END AS TotalExpense,
	       CASE 
	            WHEN y.TotalIncome IS NULL THEN 0
	            ELSE y.TotalIncome
	       END - CASE 
	                  WHEN t.TotalExpense IS NULL THEN 0
	                  ELSE t.TotalExpense
	             END AS Total
	FROM   (
	           SELECT e.StoreID,
	                  --DATEADD(dd, (DATEDIFF(dd, 0, e.ExpenseTime)), 0) AS Date,
	                  SUM(e.Amount) AS TotalExpense
	           FROM   Expenses e
	           WHERE  e.ExpenseTime >= @from
	                  AND e.ExpenseTime < @to
	           GROUP BY
	                  e.StoreID
	                  --DATEADD(dd, (DATEDIFF(dd, 0, e.ExpenseTime)), 0)
	       ) t
	       FULL OUTER JOIN (
	                SELECT p.StoreID,
	                       --DATEADD(dd, (DATEDIFF(dd, 0, p.PayTime)), 0) AS Date,
	                       SUM(p.Amount) AS TotalIncome
	                FROM   Payments p
	                WHERE  p.PayTime >= @from
	                       AND p.PayTime < @to
	                GROUP BY
	                       p.StoreID
	                       --DATEADD(dd, (DATEDIFF(dd, 0, p.PayTime)), 0)
	            ) y
	            ON  (t.StoreID = y.StoreID)-- AND t.Date = y.Date)
GO
--Import statistics for each product day by day
IF OBJECT_ID('ImportStatisticsEachDay', 'P') IS NOT NULL
	DROP PROCEDURE ImportStatisticsEachDay;
GO

CREATE PROCEDURE ImportStatisticsEachDay
	@from DATETIME,
	@to DATETIME,
	@store UNIQUEIDENTIFIER
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT Product,
		   DATEADD(dd, (DATEDIFF(dd, 0, ImportTime)), 0) AS ImportDate, 
	       SUM(Amount) AS TotalAmount
	FROM   Imports
	WHERE  (@store IS NULL OR @store =storeID)
			AND ImportTime >= @from
	       AND ImportTime < @to
	GROUP BY	       
	       Product,
		   DATEADD(dd, (DATEDIFF(dd, 0, ImportTime)), 0)
	ORDER BY ImportDate
GO

IF OBJECT_ID('ImportStatistics', 'P') IS NOT NULL
	DROP PROCEDURE ImportStatistics;
GO

--import statistic for a period of time
CREATE PROCEDURE ImportStatistics
	@from DATETIME,
	@to DATETIME
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT StoreID,
	       Product,
		   --DATEADD(dd, (DATEDIFF(dd, 0, ImportTime)), 0) AS Date, 
	       SUM(Amount) AS TotalAmount
	FROM   Imports
	WHERE  ImportTime >= @from
	       AND ImportTime < @to
	GROUP BY
	       StoreID,
	       Product
		   --DATEADD(dd, (DATEDIFF(dd, 0, ImportTime)), 0)
GO

--export statatistic for each product day by day
IF OBJECT_ID('ExportStatisticsEachDay', 'P') IS NOT NULL
	DROP PROCEDURE ExportStatisticsEachDay;
GO

CREATE PROCEDURE ExportStatisticsEachDay
	@from DATETIME,
	@to DATETIME,
	@store UNIQUEIDENTIFIER
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT Product,
		   DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0) AS ExportDate,
	       SUM(Amount) AS TotalAmount
	FROM   Sells
	WHERE  (@store IS NULL OR @store = storeID)
			AND SellTime >= @from
	       AND SellTime < @to
	GROUP BY	       
	       Product,
		   DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0)
	ORDER BY ExportDate
GO

--export statatistic for a period of time
IF OBJECT_ID('ExportStatistics', 'P') IS NOT NULL
	DROP PROCEDURE ExportStatistics;
GO

CREATE PROCEDURE ExportStatistics
	@from DATETIME,
	@to DATETIME
AS
	IF @from IS NULL BEGIN
	    SET @from = 0             	
	END
	
	IF	@to IS NULL BEGIN
		SET @to = GETDATE();  	            	
	END
	
	SELECT StoreID,
	       Product,
		   --DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0) AS Date,
	       SUM(Amount) AS TotalAmount
	FROM   Sells
	WHERE  SellTime >= @from
	       AND SellTime < @to
	GROUP BY
	       StoreID,
	       Product
		   --DATEADD(dd, (DATEDIFF(dd, 0, SellTime)), 0)
GO


--Import statistics for each product all time
IF OBJECT_ID('ImportStatisticsAll', 'P') IS NOT NULL
	DROP PROCEDURE ImportStatisticsAll;
GO

CREATE PROCEDURE ImportStatisticsAll
AS
	SELECT StoreID,
	       Product,
	       SUM(Amount) AS TotalAmount
	FROM   Imports
	GROUP BY
	       StoreID,
	       Product
GO
--export statatistic for each product all time
IF OBJECT_ID('ExportStatisticsAll', 'P') IS NOT NULL
    DROP PROCEDURE ExportStatisticsAll;
GO

CREATE PROCEDURE ExportStatisticsAll
AS
	SELECT StoreID,
	       Product,
	       SUM(Amount) AS TotalAmount
	FROM   Sells
	GROUP BY
	       StoreID,
	       Product
GO
